Imports System.IO
Imports System.Text
Imports System.data.OleDb
Public Class CSVHelper

    ''' <summary>
    ''' To define the data type of the csv file
    ''' Data Type = Text mean it will have a "speech mark" surround the text
    ''' Data Type = Number mean no "speech mark" surround the text
    ''' </summary>
    ''' <remarks></remarks>
    Enum CSVDataType
        Text
        Number
    End Enum


    Public Function ExportTblToCSV(ByVal FileName As String, ByVal tbl As DataTable, ByVal WithHeader As Boolean, _
    ByVal CustomHeader() As String, ByVal WithFormat() As CSVDataType) As Boolean
        Dim result As Boolean

        If tbl IsNot Nothing AndAlso tbl.Rows.Count > 0 Then

            If WithHeader AndAlso CustomHeader Is Nothing Then
                Throw New Exception("Custom Header is empty.")
                GoTo ERROR_Occur
            ElseIf WithHeader AndAlso CustomHeader.Length <> tbl.Columns.Count Then
                Throw New Exception("The Custom Header is not match the number of columns.")
                GoTo ERROR_Occur
            ElseIf WithFormat IsNot Nothing AndAlso WithFormat.Length <> tbl.Columns.Count Then
                Throw New Exception("The number data type does not match the number of columns.")
                GoTo ERROR_Occur
            End If

            Dim fsOutput As New FileStream(FileName, FileMode.Create)

            Dim swOutput As New StreamWriter(fsOutput)
            swOutput.AutoFlush = True
            If WithHeader Then
                If CustomHeader IsNot Nothing Then
                    swOutput.Write(GenerateCSVCustomHeader(CustomHeader))
                Else
                    swOutput.Write(GenerateCSVHeader(tbl))
                End If

            End If

            If WithFormat Is Nothing Then
                swOutput.Write(GenerateCSVBody(tbl))
            Else
                swOutput.Write(GenerateCSVBodyWithDataType(WithFormat, tbl))
            End If

            fsOutput.Flush()
            swOutput.Close()
            fsOutput.Close()
            result = True
        Else
            Throw New Exception("Datatable is empty.")
        End If
ERROR_Occur:
        Return result
    End Function

    Public Function ExportTblToCSV(ByVal FileName As String, ByVal tbl As DataTable, ByVal WithHeader As Boolean, _
   ByVal CustomHeader() As String, ByVal WithFormat() As CSVDataType, ByVal WithoutQuote As Boolean) As Boolean
        Dim result As Boolean

        If tbl IsNot Nothing AndAlso tbl.Rows.Count > 0 Then

            If WithHeader AndAlso CustomHeader Is Nothing Then
                Throw New Exception("Custom Header is empty.")
                GoTo ERROR_Occur
            ElseIf WithHeader AndAlso CustomHeader.Length <> tbl.Columns.Count Then
                Throw New Exception("The Custom Header is not match the number of columns.")
                GoTo ERROR_Occur
            ElseIf WithFormat IsNot Nothing AndAlso WithFormat.Length <> tbl.Columns.Count Then
                Throw New Exception("The number data type does not match the number of columns.")
                GoTo ERROR_Occur
            End If

            Dim fsOutput As New FileStream(FileName, FileMode.Create)

            Dim swOutput As New StreamWriter(fsOutput)
            swOutput.AutoFlush = True
            If WithHeader Then
                If CustomHeader IsNot Nothing Then
                    swOutput.Write(GenerateCSVCustomHeader(CustomHeader))
                Else
                    swOutput.Write(GenerateCSVHeader(tbl))
                End If

            End If

            If WithFormat Is Nothing Then
                If WithoutQuote Then
                    swOutput.Write(GenerateCSVBodyNoQuote(tbl))
                Else
                    swOutput.Write(GenerateCSVBody(tbl))
                End If

            Else
                swOutput.Write(GenerateCSVBodyWithDataType(WithFormat, tbl))
            End If

            fsOutput.Flush()
            swOutput.Close()
            fsOutput.Close()
            result = True
        Else
            Throw New Exception("Datatable is empty.")
        End If
ERROR_Occur:
        Return result
    End Function

    Public Function ExportTblToCSV(ByVal FileName As String, ByVal tbl As DataTable, ByVal WithHeader As Boolean, _
  ByVal CSVFormat() As CSVFormatHelper) As Boolean
        Dim result As Boolean

        If tbl IsNot Nothing AndAlso tbl.Rows.Count > 0 Then
            Dim CustomHeader() As String = Nothing
            Dim WithFormat() As CSVDataType = Nothing

            If CSVFormat IsNot Nothing Then
                Dim TempCustomHeader As New List(Of String)
                Dim TempWithFormat As New List(Of CSVDataType)
                For i As Int32 = 0 To CSVFormat.Length - 1
                    TempCustomHeader.Add(CSVFormat(i).Header)
                    TempWithFormat.Add(CSVFormat(i).DataType)
                Next
                CustomHeader = TempCustomHeader.ToArray
                WithFormat = TempWithFormat.ToArray
            End If
            ExportTblToCSV(FileName, tbl, WithHeader, CustomHeader, WithFormat)
        Else
            Throw New Exception("Datatable is empty.")
        End If

ERROR_Occur:
        Return result
    End Function


    Private Function GenerateCSVHeader(ByVal tbl As DataTable) As String
        Dim textToWrite As New StringBuilder
        Dim ColumnsCount As Int32 = tbl.Columns.Count


        For c As Int32 = 0 To ColumnsCount - 2
            textToWrite.Append(ControlChars.Quote & tbl.Columns(c).ColumnName & ControlChars.Quote & ",")
        Next
        textToWrite.Append(ControlChars.Quote & tbl.Columns(ColumnsCount - 1).ColumnName & ControlChars.Quote)
        textToWrite.AppendLine()
        Return textToWrite.ToString
    End Function

    Private Function GenerateCSVCustomHeader(ByVal CustomHeader() As String) As String
        Dim textToWrite As New StringBuilder
        Dim ColumnsCount As Int32 = CustomHeader.Length

        For c As Int32 = 0 To ColumnsCount - 2
            textToWrite.Append(ControlChars.Quote & CustomHeader(c) & ControlChars.Quote & ",")
        Next
        textToWrite.Append(ControlChars.Quote & CustomHeader(CustomHeader.Length - 1) & ControlChars.Quote)
        textToWrite.AppendLine()
        Return textToWrite.ToString
    End Function

    Private Function GenerateCSVBody(ByVal tbl As DataTable) As String
        Dim textToWrite As New StringBuilder
        Dim ColumnsCount As Int32 = tbl.Columns.Count

        For i As Int32 = 0 To tbl.Rows.Count - 1
            For j As Int32 = 0 To ColumnsCount - 2
                If (IsDBNull(tbl.Rows(i)(j))) Then
                    textToWrite.Append(ControlChars.Quote & ControlChars.Quote & ",")
                Else
                    textToWrite.Append(ControlChars.Quote & tbl.Rows(i)(j).ToString & ControlChars.Quote & ",")
                End If
            Next
            If (IsDBNull(tbl.Rows(i)(ColumnsCount - 1))) Then
                textToWrite.Append(ControlChars.Quote & ControlChars.Quote)
            Else
                textToWrite.Append(ControlChars.Quote & tbl.Rows(i)(ColumnsCount - 1).ToString & ControlChars.Quote)
            End If
            textToWrite.AppendLine()
        Next

        Return textToWrite.ToString
    End Function
    Private Function GenerateCSVBodyNoQuote(ByVal tbl As DataTable) As String
        Dim textToWrite As New StringBuilder
        Dim ColumnsCount As Int32 = tbl.Columns.Count

        For i As Int32 = 0 To tbl.Rows.Count - 1
            For j As Int32 = 0 To ColumnsCount - 2
                If (IsDBNull(tbl.Rows(i)(j))) Then
                    textToWrite.Append(",")
                Else
                    textToWrite.Append(tbl.Rows(i)(j).ToString & ",")
                End If
            Next
            If (IsDBNull(tbl.Rows(i)(ColumnsCount - 1))) Then
                textToWrite.Append("")
            Else
                textToWrite.Append(tbl.Rows(i)(ColumnsCount - 1).ToString)
            End If
            textToWrite.AppendLine()
        Next

        Return textToWrite.ToString
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="DataType"></param>
    ''' <param name="tbl"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GenerateCSVBodyWithDataType(ByVal DataType() As CSVDataType, ByVal tbl As DataTable) As String
        Dim textToWrite As New StringBuilder
        Dim ColumnsCount As Int32 = tbl.Columns.Count

        For i As Int32 = 0 To tbl.Rows.Count - 1
            For j As Int32 = 0 To ColumnsCount - 2
                If (IsDBNull(tbl.Rows(i)(j))) Then
                    textToWrite.Append(ControlChars.Quote & ControlChars.Quote & ",")
                Else
                    If DataType(j) = CSVDataType.Number Then
                        textToWrite.Append(tbl.Rows(i)(j).ToString & ",")
                    Else
                        textToWrite.Append(ControlChars.Quote & tbl.Rows(i)(j).ToString & ControlChars.Quote & ",")
                    End If

                End If
            Next
            If (IsDBNull(tbl.Rows(i)(ColumnsCount - 1))) Then
                textToWrite.Append(ControlChars.Quote & ControlChars.Quote)
            Else
                If DataType(ColumnsCount - 1) = CSVDataType.Number Then
                    textToWrite.Append(tbl.Rows(i)(ColumnsCount - 1).ToString)
                Else
                    textToWrite.Append(ControlChars.Quote & tbl.Rows(i)(ColumnsCount - 1).ToString & ControlChars.Quote)
                End If

            End If
            textToWrite.AppendLine()
        Next

        Return textToWrite.ToString
    End Function


    ''' <summary>
    ''' This method can convert a CSV file into Datatable
    ''' </summary>
    ''' <param name="FullFileName">The full name and path of the CSV file</param>
    ''' <returns>Datatable represent the CSV data</returns>
    ''' <remarks></remarks>
    Public Function CSVToDataTable(ByVal FullFileName As String, ByVal CSV_IncludeHeader As Boolean) As DataTable
        Dim tbl As New DataTable
        Dim filePath As String = Path.GetDirectoryName(FullFileName)
        Dim fileName As String = Path.GetFileName(FullFileName)
        Dim ConStrCSV As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};'", filePath)

        If CSV_IncludeHeader Then
            ConStrCSV = ConStrCSV & "Extended Properties='Text;HDR=Yes;"
        Else
            ConStrCSV = ConStrCSV & "Extended Properties='Text;HDR=No;"
        End If

        Dim objConn As New OleDbConnection(ConStrCSV)
        Dim objCmd As New OleDbCommand
        objCmd.Connection = objConn
        objCmd.CommandText = "SELECT * FROM " & fileName
        objCmd.CommandType = CommandType.Text
        Dim adapter As New OleDbDataAdapter(objCmd)
        adapter.Fill(tbl)

        Return tbl

    End Function
End Class
